CHECK constraint with User Defined Function in SQL Server

This post describes how to use result of an user defined function with CHECK constraint in SQL Server.  For demonstration, considered a situation where it is not  allowed to insert or update records where calculated age of a person is less than 18 years as per his/her Date of Birth.

So for this first we need to create the function before creating the CHECK constraint. Here is a function which will return age as per the date of birth provided.

/*
This function will take Date Of Birth as input parameter,
and returns Age in Years to the caller
*/
CREATE FUNCTION [dbo].[fnGetAge](@DateOfBirth DATETIME)
RETURNS SMALLINT
AS
BEGIN
DECLARE @Age SMALLINT
SET @Age =(DATEDIFF(YY, @DateOfBirth, GETDATE())-
(CASE
WHEN GETDATE() >= DATEADD(YY, DATEDIFF(YY, @DateOfBirth, GETDATE()), @DateOfBirth) THEN 0
ELSE 1
END))
RETURN @Age
END;
GO

Now create a table where CHECK constraint will refer to this function to check if the age of the person meets the required criteria or not (minimum 18 Years in this case).

--Create Customer table
CREATE TABLE Customers
(
CustID INT IDENTITY(1,1) NOT NULL,
CustName VARCHAR(100) NOT NULL,
DateOfBirth DATETIME NOT NULL,
Email VARCHAR(100),
CONSTRAINT pkCustomers PRIMARY KEY(CustID),
--Calculate & check if age of customer is atleast 18 Years
CONSTRAINT chkCheckAge CHECK(dbo.fnGetAge(DateOfBirth) >= 18)
)
GO

--Populate table with some sample data.
INSERT INTO Customers(CustName, DateOfBirth, Email)
VALUES ('ABC','19810726','abc@cust.info'),
('XYZ','19840510','xyz@cust.info'),
('MNO','19720417','mno@cust.info')
GO
--Result Message
--(3 row(s) affected)

Now try to insert a record where calculated age is less than 18 years and see what happens.

--Try to insert a record where Age less than 18 Years(as per provided Date of Birth)
INSERT INTO Customers(CustName, DateOfBirth, Email)
VALUES ('TEST','20010315','test@cust.info')
GO

/*
--Error Message
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "chkCheckAge".
The conflict occurred in database "SQLJourney", table "dbo.Customers", column 'DateOfBirth'.
The statement has been terminated.
*/

As the age does not meet the required criteria in defined CHECK constraint, it doesn’t allow to insert this record to the table.

Note:

CHECK constraint evaluates the provided expression while UPDATE operation as well.

You cannot DROP the function till the table (which refers to that function) exists in the database.